package com.wendyxie.sproutup.db

import android.content.ContentValues
import android.content.Context
import android.database.sqlite.SQLiteDatabase
import android.os.Build
import android.util.Log
import androidx.annotation.RequiresApi
import com.wendyxie.sproutup.entity.UserScoreTmplEntity
import com.wendyxie.sproutup.enum.ScoreFrequencyEnum
import com.wendyxie.sproutup.utils.RandomUtil
import java.time.LocalDateTime
import java.time.format.DateTimeFormatter
import java.util.*


class SQLUserScoreTmplHelper {

    // 定义静态变量
    companion object {
        private const val TABLE_NAME = "user_score_tmpl"
        private const val KEY_ID = "id"
        private const val KEY_USER_ID = "user_id"
        private const val KEY_BABY_ID = "baby_id"
        private const val KEY_SCORE = "score"
        private const val KEY_FREQUENCY = "frequency" // DAY-每天 ，WEEK-每周 1-每天，2-每周，3-每月，4-每年
        private const val KEY_SCORE_TITLE = "score_title"
        private const val KEY_IS_AUTO = "is_auto"//是否自动,1-自动，0-一次性
        private const val KEY_CREATE_TIME = "create_time"
        private const val KEY_UPDATE_TIME = "update_time"


        /**
         * 创建表
         */
        fun createUserScoreTmplTable(db: SQLiteDatabase) {
            val createScoreTmplTable = "CREATE TABLE user_score_tmpl (" +
                    "$KEY_ID varchar(36) not null PRIMARY KEY, " +
                    "$KEY_USER_ID varchar(36) default '', " + // 用户id
                    "$KEY_BABY_ID varchar(36) default '', " + // 宝宝编号
                    "$KEY_SCORE int," + // 用户得分
                    "$KEY_FREQUENCY varchar(36) default ${ScoreFrequencyEnum.ONCE.value}, " + // 频率 ONCE单次，DAY每日，WEEK每周，MONTH每月
                    "$KEY_SCORE_TITLE varchar(100), " + // 得分原因
                    "$KEY_IS_AUTO int default 0, " + // 是否自动:0-不， 1-自动
                    "$KEY_CREATE_TIME datetime DEFAULT CURRENT_TIMESTAMP ," +
                    "$KEY_UPDATE_TIME datetime DEFAULT CURRENT_TIMESTAMP )"
            db.execSQL(createScoreTmplTable)
        }

        /**
         * 新增记录
         */
        fun addUserScoreTmpl(userScoreTmplEntity: UserScoreTmplEntity, db: SQLiteDatabase) {
            // 创建 ContentValues 对象，用于存储插入的数据
            val contentValues = ContentValues()
            contentValues.put(KEY_ID, RandomUtil.getUUID())
            contentValues.put(KEY_USER_ID, userScoreTmplEntity.userId)
            contentValues.put(KEY_BABY_ID, userScoreTmplEntity.babyId)
            contentValues.put(KEY_SCORE, userScoreTmplEntity.score)
            contentValues.put(KEY_FREQUENCY, userScoreTmplEntity.frequency)
            contentValues.put(KEY_SCORE_TITLE, userScoreTmplEntity.scoreTitle)
            contentValues.put(KEY_IS_AUTO, userScoreTmplEntity.isAuto)
            contentValues.put(
                KEY_CREATE_TIME,
                userScoreTmplEntity.createTime.format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"))
            )
            contentValues.put(
                KEY_UPDATE_TIME,
                userScoreTmplEntity.updateTime.format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"))
            )
            db.insertOrThrow(TABLE_NAME, null, contentValues)
        }

        /**
         * 删除记录
         */
        fun deleteUserScoreTmpl(id: String, db: SQLiteDatabase) {
            val whereClause = "$KEY_ID = ?"
            val whereArgs = arrayOf(id)
            Log.d("deleteUserScoreTmpl", "whereClause: $whereClause, whereArgs: ${whereArgs[0]}")
            db.delete(TABLE_NAME, whereClause, whereArgs)
        }

        /**
         * 查询需自动记录的任务
         */
        fun queryListByBabyIdAndAuto(babyId: String, db: SQLiteDatabase): List<UserScoreTmplEntity> {
            val selection = "$KEY_BABY_ID = ? and $KEY_IS_AUTO = ?"
            val selectionArgs = mutableListOf<String>(babyId, "1")
            val cursor = db.query(TABLE_NAME, null, selection, selectionArgs.toTypedArray(), null, null, null)
            val list = mutableListOf<UserScoreTmplEntity>()
            while (cursor.moveToNext()) {
                list.add(cursorToUserScoreTmplEntity(cursor))
            }
            cursor.close()
            return list
        }

        /**
         * 查询记录
         */
        fun getUserScoreTmplList(pageSize: Int, page: Int, db: SQLiteDatabase): List<UserScoreTmplEntity> {
            val sql = "SELECT * FROM $TABLE_NAME order by $KEY_IS_AUTO, $KEY_CREATE_TIME desc limit ? offset ?"
            val params = arrayOf(pageSize.toString(), page.toString())
            val cursor = db.rawQuery(sql, params)
            val scoreTmplList = mutableListOf<UserScoreTmplEntity>()
            while (cursor.moveToNext()) {
                scoreTmplList.add(cursorToUserScoreTmplEntity(cursor))
            }
            cursor.close()
            return scoreTmplList
        }

        private fun cursorToUserScoreTmplEntity(cursor: android.database.Cursor): UserScoreTmplEntity {
            return UserScoreTmplEntity(
                cursor.getString(cursor.getColumnIndex(KEY_ID)),
                cursor.getString(cursor.getColumnIndex(KEY_USER_ID)),
                cursor.getString(cursor.getColumnIndex(KEY_BABY_ID)),
                cursor.getInt(cursor.getColumnIndex(KEY_SCORE)),
                cursor.getString(cursor.getColumnIndex(KEY_FREQUENCY)),
                cursor.getString(cursor.getColumnIndex(KEY_SCORE_TITLE)),
                cursor.getInt(cursor.getColumnIndex(KEY_IS_AUTO)),
                LocalDateTime.parse(
                    cursor.getString(cursor.getColumnIndex(KEY_CREATE_TIME)),
                    DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss")
                ),
                LocalDateTime.parse(
                    cursor.getString(cursor.getColumnIndex(KEY_UPDATE_TIME)),
                    DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss")
                ),
            )
        }

    }


}
